<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>Section 7.2.&nbsp; Structured Query Language</title>
<link rel="STYLESHEET" type="text/css" href="images/style.css">
<link rel="STYLESHEET" type="text/css" href="images/docsafari.css">
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-7-SECT-1.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-7-SECT-3.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<br><table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td valign="top"><a name="learnphpmysql-CHP-7-SECT-2"></a>
<h3 id="title-IDAALMAJ" class="docSection1Title">7.2. Structured Query Language</h3>
<a name="IDX-CHP-7-0314"></a> 

<p class="docText">Now that you've defined a table, you can add data to it. MySQL will keep track of all the details. To manipulate data, use the Structured Query Language (SQL) commands. Because it's been designed to easily describe the relationship between tables and rows, the database uses SQL to modify data in tables.</p>
<p class="docText">SQL is a standard language used with any database such as MySQL, Oracle, or Microsoft SQL Server. It was developed specifically as a language used to retrieve, add, and manipulate data that resides in databases. We'll get into the nitty gritty of MySQL in <a class="docLink" href="learnphpmysql-CHP-8.html#learnphpmysql-CHP-8">Chapter 8</a>, but we'll start with some easy-to-use commands. We're going to start with creating tables.</p>
<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><tr><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><tr><td width="60" valign="top"><img src="images/tip_yellow.jpg" width="50" height="54" alt=""></td><td valign="top">
<p class="docText">Each database adds on its own extensions to the standard SQL. These are usually more advanced capabilities, such as an outer join. An <span class="docEmphasis">outer join</span> is a special way of linking two tables so that data from one of the tables is included even if there isn't a match in the other table. The syntax for outer joins for tables in Oracle is a plus within parentheses <tt>(+)</tt>, whereas MySQL uses the syntax <tt>left join on</tt> to perform an outer table join.</p>
</TD></tr></table></td></TR></table></P><br>
<a name="learnphpmysql-CHP-7-SECT-2.1"></a>
<H4 id="title-IDADMMAJ" class="docSection2Title">7.2.1. Creating Tables</h4>
<a name="IDX-CHP-7-0315"></a> 
<a name="IDX-CHP-7-0316"></a> 
<a name="IDX-CHP-7-0317"></a> 
<a name="IDX-CHP-7-0318"></a> 

<p class="docText">Use the <tt>create table</tt><a name="IDX-CHP-7-0319"></a> 
 command to specify the structure of new database tables. When you create a database table, each column has a few options, in addition to the column names and data types. Values that must be supplied when adding data to a table use the <tt>NOT NULL</tt> keyword. The <tt>PRIMARY KEY</tt> keyword tells MySQL which column to use as a key field. Then, you have MySQL automatically assign key values by using the <tt>AUTO_INCREMENT</tt> keyword.</p>
<p class="docText">To create these tables, paste the code into the MySQL command-line client.</p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-7-EX-1">Example 7-1</a> creates the <tt>books</tt> table using the data types from <a class="docLink" href="learnphpmysql-CHP-7-SECT-1.html#learnphpmysql-CHP-7-TABLE-8">Table 7-8</a>.</P>
<a name="learnphpmysql-CHP-7-EX-1"></a><h5 id="title-IDA4NMAJ" class="docExampleTitle">Example 7-1. Creating the books and authors tables</h5><P><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
CREATE TABLE books (
title_id INT NOT NULL AUTO_INCREMENT,
title VARCHAR (150),
pages INT,
PRIMARY KEY (title_id));
CREATE TABLE authors (
author_id INT NOT NULL AUTO_INCREMENT,
title_id INT NOT NULL,
author VARCHAR (125),
PRIMARY KEY (author_id));
</pre><BR>

</td></tr></table></p>
<p class="docText">If everything is OK, you'll see output that instructs MySQL to create a table called "books," and it'll look like this (the time the query takes to run may be different than 0.06 sec):</p>
<pre>
mysql&gt; CREATE TABLE books (
    -&gt; title_id INT NOT NULL AUTO_INCREMENT,
    -&gt; title VARCHAR (150),
    -&gt; pages INT,
    -&gt; PRIMARY KEY (title_id));
Query OK, 0 rows affected (0.06 sec)
mysql&gt; CREATE TABLE authors (
    -&gt; author_id INT NOT NULL AUTO_INCREMENT,
    -&gt; title_id INT,
    -&gt; author VARCHAR (125),
    -&gt; PRIMARY KEY (author_id));
Query OK, 0 rows affected (0.06 sec)
</pre><BR>

<p class="docText">The code breaks down as follows:</P>
<ul><li><p class="docList">The first column, called <tt>title_id</tt>, is an integer. The <tt>auto_increment</tt> keyword is a unique value assigned to this field automatically during row insertion.</p></li><LI><p class="docList">The <tt>title</tt> column holds text up to 150 characters.</p></LI><li><p class="docList">The <tt>pages</tt> column is an integer.</P></LI><LI><p class="docList">The <tt>PRIMARY KEY</tt> field tells MySQL which field is the key value. While this field isn't required, it allows MySQL to speed up access when you retrieve data from multiple tables or a specific row using the key value. MySQL does this by using a special data structure called an index. An <span class="docEmphasis">index</span> acts like a shortcut for finding a record, like a card catalog in a library. To verify your table columns, use <tt>DESCRIBE</tt>:</p><pre>
DESCRIBE books;
</pre><br>
</li></ul>
<p class="docText">This returns:</p>
<pre>
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| title_id | int(11)      |      | PRI | NULL    | auto_increment |
| title    | varchar(150) | YES  |     | NULL    |                |
| pages    | int(11)      | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
</pre><br>

<p class="docText">And the following:</p>
<pre>
describe authors;
</pre><br>

<p class="docText">returns:</p>
<pre>
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| author_id | int(11)      |      | PRI | NULL    | auto_increment |
| title_id  | int(11)      |      |     | 0       |                |
| author    | varchar(125) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
</pre><br>

<p class="docText">Everything is as we specified in our description.</p>
<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><tr><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><TR><td width="60" valign="top"><img src="images/tip_yellow.jpg" width="50" height="54" alt=""></td><TD valign="top">
<p class="docText">Notice that because we didn't specify the size of the integer columns, MySQL used the default of 11 places.</P>
</td></TR></table></td></tr></table></p><BR>

<a name="learnphpmysql-CHP-7-SECT-2.2"></a>
<h4 id="title-IDAKQMAJ" class="docSection2Title">7.2.2. Adding Data to a Table</h4>
<a name="IDX-CHP-7-0320"></a> 
<a name="IDX-CHP-7-0321"></a> 
<a name="IDX-CHP-7-0322"></a> 
<a name="IDX-CHP-7-0323"></a> 

<p class="docText">The <tt>insert</tt><a name="IDX-CHP-7-0324"></a> 
 command is used to add data. Its syntax is <tt>INSERT INTO</tt> <tt><I>table</i></tt> <tt>VALUES (</tt><tt><i>[values]</I></tt><tt>);</tt>. This syntax displays which table data needs to be added to, and a list of the values. They should be in the same order they were defined when the table was created (as long as you don't skip any column values). There are specific rules for how you handle data to populate your database using SQL commands.</p>
<ul><li><p class="docList">Numeric values shouldn't be quoted.</p></LI><LI><p class="docList">String values should always be quoted.</p></li><li><p class="docList">Date and time values should always be quoted.</p></LI><li><p class="docList">Functions shouldn't be quoted.</P></li><LI><p class="docList">NULL should always be quoted.</P></LI></ul>
<p class="docText">Lastly, if a row isn't given a value, it automatically is considered <tt>NULL</tt>. However, if a column can't have <tt>NULL</tt>, even if it was set to <tt>NOT NULL</tt>; if you don't specify a value, an error is created.</p>
<p class="docText">For example:</p>
<pre>
INSERT INTO books VALUES (1,"Linux in a Nutshell",112);
INSERT INTO authors VALUES (1,1,"Ellen Siever");
INSERT INTO authors VALUES (2,1,"Aaron Weber");
</pre><br>

<p class="docText">As long as there were no errors, you should get:</p>
<pre>
mysql&gt; INSERT INTO books VALUES (1,"Linux in a Nutshell",112);
Query OK, 1 row affected (0.00 sec)

mysql&gt; INSERT INTO authors VALUES (NULL,1,"Ellen Siever");
ES (2,1Query OK, 1 row affected (0.00 sec)

,"Aaron Weber");
mysql&gt; INSERT INTO authors VALUES (NULL,1,"Aaron Weber");
Query OK, 1 row affected (0.00 sec)
</pre><br>

<p class="docText">When adding data, you must specify all the columns even if you aren't supplying a value for each one. Even though we didn't supply the <tt>author_id</tt> field and we let MySQL assign it for us, we still had to leave a placeholder for it.</p>
<p class="docText">Likewise, we add the other book:</p>
<pre>
INSERT INTO books VALUES (2,"Classic Shell Scripting",256);
INSERT INTO authors VALUES (NULL,2,"Arnold Robbins");
INSERT INTO authors VALUES (NULL,2,"Nelson Beebe");
</pre><br>

<p class="docText">This gives us two rows in the <tt>books</tt> table. Now that you know how to create a table and enter data into it, you'll need to know how to view that information.</p>

<a name="learnphpmysql-CHP-7-SECT-2.3"></a>
<h4 id="title-IDAJTMAJ" class="docSection2Title">7.2.3. Querying the Database</h4>
<a name="IDX-CHP-7-0325"></a> 
<a name="IDX-CHP-7-0326"></a> 
<a name="IDX-CHP-7-0327"></a> 
<a name="IDX-CHP-7-0328"></a> 
<a name="IDX-CHP-7-0329"></a> 

<p class="docText">Having data in tables doesn't do much good if you can't view what's in it. The <tt>SELECT</tt><a name="IDX-CHP-7-0330"></a> 
 command specifies which table(s) to query and which rows to view based on specific conditions. The syntax of <tt>SELECT</tt> is <tt>SELECT</tt> <tt><i>columns</i></tt> <tt>FROM</tt> <tt><I>tables</i></tt> <tt>[WHERE</tt> <tt><i>CLAUSE</I></tt><tt>];</tt>.</P>
<p class="docText">Columns indicate a list of columns to display from the selected tables. The <tt>WHERE</tt> clause optionally restricts which rows are selected. <tt>WHERE</tt> provides limits to the results that are returned from a query. For example, rows can be rejected if a field doesn't equal a literal value or is less than or greater than a value. Fields from multiple tables can be forced to be equal. If multiple tables are included in a <tt>SELECT</tt> statement without a <tt>WHERE</tt> clause, the resultant set becomes the <span class="docEmphasis">Cartesian</span> product, in which every row in the first table is returned with all rows in the second table followed by the same thing for the second row in the first table. To put it another way, that's a lot of results!</p>
<p class="docText">The simplest query is to view all data in a table:</P>
<pre>
SELECT * FROM books;
</pre><br>

<p class="docText">This displays:</p>
<pre>
+----------+-------------------------+-------+
| title_id | title                   | pages |
+----------+-------------------------+-------+
|        1 | Linux in a Nutshell     |   112 |
|        2 | Classic Shell Scripting |   256 |
+----------+-------------------------+-------+
2 rows in set (0.01 sec)
</pre><br>

<p class="docText">And the following:</P>
<pre>
SELECT * FROM authors;
</pre><br>

<p class="docText">displays:</p>
<pre>
+-----------+----------+-------------------+
| author_id | title_id | author            |
+-----------+----------+-------------------+
|         1 |        1 | Ellen Siever      |
|         2 |        1 | Aaron Weber       |
|         3 |        2 | Arnold Robbins    |
|         4 |        2 | Nelson Beebe      |
+-----------+----------+-------------------+
5 rows in set (0.01 sec)
</pre><BR>

<a name="learnphpmysql-CHP-7-SECT-2.3.1"></a>
<h5 id="title-IDA2VMAJ" class="docSection3Title">7.2.3.1. Limit results with WHERE</h5>
<a name="IDX-CHP-7-0331"></a> 
<a name="IDX-CHP-7-0332"></a> 
<a name="IDX-CHP-7-0333"></a> 
<a name="IDX-CHP-7-0334"></a> 

<p class="docText">If you're only interested in the title <tt>Classic Shell Scripting</tt>, you can use a <tt>WHERE</tt> clause to restrict your query:</P>
<pre>
SELECT * FROM books WHERE title=('Classic Shell Scripting');
</pre><br>

<p class="docText">This returns:</p>
<pre>
+----------+-------------------------+-------+
| title_id | title                   | pages |
+----------+-------------------------+-------+
|        2 | Classic Shell Scripting |   256 |
+----------+-------------------------+-------+
1 row in set (0.00 sec)
</pre><br>

<p class="docText">You can also list out just the columns you're interested in from a table by using:</p>
<pre>
SELECT pages FROM books WHERE title=('Classic Shell Scripting');
</pre><BR>

<p class="docText">This returns:</P>
<pre>
+-------+
| pages |
+-------+
|   256 |
+-------+

1 row in set (0.00 sec)
</pre><br>

<p class="docText">Conditions come after the <tt>WHERE</tt> clause and should be enclosed by parentheses (<tt>()</tt>). This forces the condition to be evaluated. Additionally, parentheses are a good idea, since you'll need them when you have nested conditions in complex queries. Getting into the habit of doing this from the beginning is best. At some point, you might want to display data from multiple tables in a query. You should also get into the habit of using the full <tt>TABLE.COLUMN</tt> reference. This prevents confusion when selecting columns if both tables have a column with the same name. For example, if two tables include a <tt>description</tt> field, it may not be clear which description to include in the query unless the full reference is included.</p>

<a name="learnphpmysql-CHP-7-SECT-2.3.2"></a>
<h5 id="title-IDAZXMAJ" class="docSection3Title">7.2.3.2. Specifying the order</h5>
<a name="IDX-CHP-7-0335"></a> 
<a name="IDX-CHP-7-0336"></a> 
<a name="IDX-CHP-7-0337"></a> 
<a name="IDX-CHP-7-0338"></a> 
<a name="IDX-CHP-7-0339"></a> 
<a name="IDX-CHP-7-0340"></a> 
<a name="IDX-CHP-7-0341"></a> 

<p class="docText">The <tt>ORDER BY</tt> keyword can be used to change the order of the results from a query. The default for <tt>ORDER BY</tt> is ascending, so if you want alphabetical order for the <tt>author</tt> column, you would just type in <tt>ORDER BY author</tt>. To select in reverse order, add the <tt>DESC</tt> keyword after <tt>author</tt>. For example, to select the authors in alphabetical order:</P>
<pre>
SELECT * FROM authors ORDER BY author;
</pre><br>

<p class="docText">This displays:</P>
<pre>
+-----------+----------+-------------------+
| author_id | title_id | author            |
+-----------+----------+-------------------+
|         2 |        1 | Aaron Weber       |
|         5 |        9 | Alex Martelli     |
|         3 |        2 | Arnold Robbins    |
|         1 |        1 | Ellen Siever      |
|         4 |        2 | Nelson Beebe      |
+-----------+----------+-------------------+
</pre><br>

<p class="docText">Next, we'll select from more than one table.</P>

<a name="learnphpmysql-CHP-7-SECT-2.3.3"></a>
<H5 id="title-IDA5ZMAJ" class="docSection3Title">7.2.3.3. Joining tables together</H5>
<p class="docText">The <tt>SELECT</tt> statement allows you to query more than one table at a time. <a class="docLink" href="#learnphpmysql-CHP-7-EX-2">Example 7-2</a> creates the <tt>purchases</tt> table and adds a couple of sample entries.</p>
<a name="learnphpmysql-CHP-7-EX-2"></a><h5 id="title-IDAR0MAJ" class="docExampleTitle">Example 7-2. The SQL to create and populate a purchases table that links user_ids and title_ids to a purchase_id</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
CREATE TABLE `purchases` (
purchase_id int(11) NOT NULL auto_increment,
user_id varchar(10) NOT NULL,
title_id int(11) NOT NULL,
purchased timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY  (purchase_id));
INSERT INTO `purchases` VALUES (1, 'mdavis', 2, '2005-11-26 17:04:29');
INSERT INTO `purchases` VALUES (2, 'mdavis', 1, '2005-11-26 17:05:58');
</pre><br>

</td></tr></table></p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-7-EX-2">Example 7-2</a> returns:</p>
<pre>
SELECT * FROM purchases;
+-------------+---------+----------+---------------------+
| purchase_id | user_id | title_id | purchased           |
+-------------+---------+----------+---------------------+
|           1 | mdavis  |        2 | 2005-11-26 17:04:29 |
|           2 | mdavis  |        1 | 2005-11-26 17:05:58 |
+-------------+---------+----------+---------------------+
2 rows in set (0.00 sec)
</pre><br>

<p class="docText">To create a query that lists the purchases, author, and pages, enter the following <tt>SELECT</tt> statement:</p>
<pre>
SELECT books.*, author FROM books, authors WHERE books.title_id = authors.title_id;
</pre><br>

<p class="docText">which produces:</P>
<pre>
+----------+-------------------------+-------+-------------------+
| title_id | title                   | pages | author            |
+----------+-------------------------+-------+-------------------+
|        1 | Linux in a Nutshell     |   112 | Ellen Siever      |
|        1 | Linux in a Nutshell     |   112 | Aaron Weber       |
|        2 | Classic Shell Scripting |   256 | Arnold Robbins    |
|        2 | Classic Shell Scripting |   256 | Nelson Beebe      |
+----------+-------------------------+-------+-------------------+
4 rows in set (0.00 sec)
</pre><br>

<p class="docText">The <tt>books.*, author</tt> portion tells the database to select all of the fields from the <tt>books</tt> table but only the author from the <tt>authors</tt> table. The <tt>WHERE books.title_id = authors.title_id</tt> portion links the tables together by the <tt>title_id</tt>.</p>
<p class="docText">You could have selected <tt>*</tt>, which includes all the fields from both tables. But the <tt>title_id</tt> field would be included twice, since it's in both tables. There's no limit to how many tables and columns you can join together.</P>
<p class="docText">As you've noticed, SQL is a combination of alpha characters and symbols used in mathematics. The <span class="docEmphasis">structure</span> in SQL means it uses English phrases to define an action but uses math-like symbols to make comparisons. Remember this analogy, and it should make it easier to remember that you always need a math-like symbol in your context. Then it will be easier for you to catch your errors!</P>

<a name="learnphpmysql-CHP-7-SECT-2.3.4"></a>
<h5 id="title-IDAI2MAJ" class="docSection3Title">7.2.3.4. Natural joins</H5>
<p class="docText">You can specify the <tt>NATURAL JOIN</tt> keyword to accomplish the same query as above with less typing. With natural joining, MySQL can take two tables and automatically join the fields that have the same name. In the case of the two tables you're working with, that's the <tt>title_id</tt> field. It also knows not to display <tt>title_id</tt> twice and not to display the <tt>author_id</tt> for author. The following:</p>
<pre>
SELECT * FROM books NATURAL JOIN authors;
</pre><br>

<p class="docText">produces:</p>
<pre>
+----------+-------------------------+-------+-----------+-------------------+
| title_id | title                   | pages | author_id | author            |
+----------+-------------------------+-------+-----------+-------------------+
|        1 | Linux in a Nutshell     |   112 |         1 | Ellen Siever      |
|        1 | Linux in a Nutshell     |   112 |         2 | Aaron Weber       |
|        2 | Classic Shell Scripting |   256 |         3 | Arnold Robbins    |
|        2 | Classic Shell Scripting |   256 |         4 | Nelson Beebe      |
+----------+-------------------------+-------+-----------+-------------------+
4 rows in set (0.00 sec)
</pre><BR>


<a name="learnphpmysql-CHP-7-SECT-2.3.5"></a>
<h5 id="title-IDAA3MAJ" class="docSection3Title">7.2.3.5. Aliases</h5>
<a name="IDX-CHP-7-0342"></a> 

<p class="docText">Use aliases<a name="IDX-CHP-7-0343"></a> 
<a name="IDX-CHP-7-0344"></a> 
<a name="IDX-CHP-7-0345"></a> 
<a name="IDX-CHP-7-0346"></a> 
 when listing which tables to include in your query. The <tt>AS</tt> keyword comes after the full table name and before the alias. In this example, "books" is aliased to <tt>b</tt> and "purchases" to <tt>p</tt>:</P>
<pre>
SELECT * FROM books AS p,authors AS b WHERE b.title_id = <b>p.title_id;</b>
</pre><br>

<p class="docText">This results in:</P>
<pre>
+----------+-------------------------+-------+-----------+----------+--------+
| title_id | title                   | pages | author_id | title_id | author
        |
+----------+-------------------------+-------+-----------+----------+-------------+
|        1 | Linux in a Nutshell     |   112 |         1 |        1 | Ellen Siever
|        1 | Linux in a Nutshell     |   112 |         2 |        1 | Aaron Weber
|        2 | Classic Shell Scripting |   256 |         3 |        2 | Arnold Robbins
|        2 | Classic Shell Scripting |   256 |         4 |        2 | Nelson Beebe
+----------+-------------------------+-------+-----------+----------+-------------+
4 rows in set (0.00 sec)
</pre><br>

<p class="docText">Once you alias a table in a query, you must refer to the table as the alias everywhere in the query. Aliases are useful for replacing long table names with a short abbreviation. They also allow you to include the same table twice in a query and to be able to specify which instance of that table you're referencing.</p>


<a name="learnphpmysql-CHP-7-SECT-2.4"></a>
<h4 id="title-IDAY4MAJ" class="docSection2Title">7.2.4. Modifying Database Data</h4>
<p class="docText">If you make a mistake, say, by entering the wrong number of pages for a book, you can change data by using the <tt>UPDATE</tt> command.</P>
<p class="docText"><tt>UPDATE</tt> uses the same <tt>WHERE</tt> clause as the <tt>SELECT</tt> statement but adds a <tt>SET</tt> command that specifies a new column value.</P>
<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><tr><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><tr><TD width="60" valign="top"><img src="images/warning_yellow.jpg" width="51" height="36" alt=""></td><TD valign="top">
<p class="docText">If you forget to include the <tt>WHERE</tt> clause for an update, it changes every record in the table.</p>
</TD></TR></table></TD></tr></table></p><br>
<p class="docText">For example, you'll update the <tt>books</tt> table:</p>
<pre>
UPDATE books SET pages = 476 WHERE title = "Linux in a Nutshell";
</pre><br>

<p class="docText">The example returns:</p>
<pre>
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
</pre><br>

<p class="docText">This changes any book with the title <span class="docEmphasis">Linux in a Nutshell</span> to 476 pages. Modifying the data cleans up any data errors you might have made.</p>
<pre>
SELECT * FROM books;
</pre><br>

<p class="docText">This returns:</p>
<pre>
+----------+-------------------------+-------+
| title_id | title                   | pages |
+----------+-------------------------+-------+
|        1 | Linux in a Nutshell     |   476 |
|        2 | Classic Shell Scripting |   256 |
+----------+-------------------------+-------+
2 rows in set (0.00 sec)
</pre><br>


<a name="learnphpmysql-CHP-7-SECT-2.5"></a>
<h4 id="title-IDARANAJ" class="docSection2Title">7.2.5. Deleting Database Data</h4>
<a name="IDX-CHP-7-0347"></a> 
<a name="IDX-CHP-7-0348"></a> 
<a name="IDX-CHP-7-0349"></a> 
<a name="IDX-CHP-7-0350"></a> 

<p class="docText">This command is used to delete rows or records in a table. The <tt>DELETE</tt> command takes the same <tt>WHERE</tt> clause as <tt>UPDATE</tt> but deletes any rows that match. Without the <tt>WHERE</tt> clause, you'd have an "oops!" moment, because all records in the table would be deleted.</p>
<pre>
DELETE FROM authors WHERE author= "Ellen Siever";
</pre><BR>

<p class="docText">In this example, only Ellen Siever's book is deleted from the database.</p>

<a name="learnphpmysql-CHP-7-SECT-2.6"></a>
<h4 id="title-IDA1BNAJ" class="docSection2Title">7.2.6. Search Functions</H4>
<p class="docText">As you have seen in the above examples, MySQL has the ability to find specific search data. However, we have not covered general search syntax. The <tt>%</tt> character in MySQL is the wildcard character and is used with the <tt>LIKE</tt> keyword. That is, it can literally represent anything. Sort of like searching in DOS, or even in the Windows Explorer Search field where <tt>*.doc</tt> means any document, regardless of the name before the <span class="docEmphasis">.doc</span> ending displays.</P>
<p class="docText">For example, to do a general search, you would use the following syntax:</p>
<pre>
SELECT * FROM authors WHERE author LIKE "%b%";
</pre><BR>

<p class="docText">This returns:</p>
<pre>
+-----------+----------+-------------------+
| author_id | title_id | author            |
+-----------+----------+-------------------+
|         2 |        1 | Aaron Weber       |
|         3 |        2 | Arnold Robbins    |
|         4 |        2 | Nelson Beebe      |
+-----------+----------+-------------------+
3 rows in set (0.00 sec)
</pre><br>

<p class="docText">This results in finding anything with the letter <tt>b</tt> in the column. Notice that two <tt>%</tt> signs were used. This checks for anything before or after that letter. You can use just one if you like, but there is no hard and fast rule that one or two be used.</p>
<p class="docText">You can place the <tt>%</tt> sign anywhere within the query's <tt>LIKE</tt> string, as the search is based upon the placement of this character.</P>
<p class="docText">Another wildcard character is the <tt>_</tt> character. It will match exactly one character. To use a literal wildcard character in your searches:</p>
<pre>
SELECT * FROM authors WHERE author like "Aaron Webe_"
</pre><br>

<p class="docText">This returns all the records containing an author name that starts with "Aaron Webe" and can have any letter for the last character of the name.</P>

<a name="learnphpmysql-CHP-7-SECT-2.7"></a>
<h4 id="title-IDAMDNAJ" class="docSection2Title">7.2.7. Logical Operators</h4>
<a name="IDX-CHP-7-0351"></a> 
<a name="IDX-CHP-7-0352"></a> 
<a name="IDX-CHP-7-0353"></a> 

<p class="docText">The same logical operators that we discussed with PHP's conditional logic can also be used in the <tt>WHERE</tt> clause.</P>
<p class="docText">You can use <tt>AND, OR</tt>, and <tt>NOT</tt> in your query's <tt>WHERE</tt> clause.</p>
<pre>
SELECT * FROM authors WHERE NOT (author = "Ellen Siever" );
</pre><br>

<p class="docText">This returns all records without Ellen Siever as the author. The parentheses are important, as they relate the <tt>NOT</tt> operator to the author comparison.</p>
<pre>
SELECT *
  FROM books, authors
 WHERE (title = "Linux in a Nutshell")
   AND (author = "Aaron Weber"
);
</pre><br>

<p class="docText">This query returns all records with author names of either Aaron Weber or Ellen Siever.</P>
<pre>
SELECT *
  FROM books, authors
 WHERE (author = "Aaron Weber")
    OR (author = "Ellen Siever")
</pre><BR>

<p class="docText">Now that all the basics have been covered, start getting excited. In our next chapter, we'll walk through using PHP to connect and work with MySQL data. We're well on our way to creating that blog at the end of the book.</p>


<a href="11011536.html"><img src="images/pixel.jpg" alt="" width="1" height="1" border="0"></a></TD></TR></table>
<br>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-7-SECT-1.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-7-SECT-3.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<script type="text/javascript"><!--
google_ad_client = "pub-0203281046321155";
google_alternate_ad_url = "http://www.bookhtml.com/adbrite.htm";
google_ad_width = 728;
google_ad_height = 90;
google_ad_format = "728x90_as";
google_ad_type = "text_image";
google_ad_channel ="4867465545";
google_color_border = "FFFFFF";
google_color_link = "0000FF";
google_color_bg = "FFFFFF";
google_color_text = "000000";
google_color_url = "0000FF";
//--></script>
<script type="text/javascript"
  src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</html>
